﻿/*
 * Added by laoxu 2013-12-25 17:00:00
 * ---------------------------------------------------------------
 * for：added the PersitenceLayer portal class,
 * all the crud operate work with this class,
 * all the unit work method with this class
 * ---------------------------------------------------------------
 * version:1.0
 * mail:lovexurongquan@163.com
 */

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.RegularExpressions;
using HouodeIT.CloudePlatform.Common.Enum;
using HouodeIT.CloudePlatform.Common.Sql;
using HouodeIT.CloudePlatform.IPL;
using HouodeIT.CloudePlatform.PL.Factory;
using PetaPoco;

namespace HouodeIT.CloudePlatform.PL
{
    public class PLSession : IPLSession
    {

        #region Unit and others

        /// <summary>
        /// PersitenceLayer portal class
        /// </summary>
        public PLSession()
        {
            _storeQueue = new Queue();
        }

        /// <summary>
        /// this will make the PLSession is not work in first.
        /// </summary>
        static PLSession()
        {
        }
        /// <summary>
        /// Store data temporarily for Queue
        /// </summary>
        internal class QueueData
        {
            /// <summary>
            /// entity state, sp : Added, Edit, Delete, Text, StoredProcedure.
            /// </summary>
            public EntityState State { get; set; }
            /// <summary>
            /// The sql for state while state is text. 
            /// </summary>
            public string Text { get; set; }
            /// <summary>
            /// Store the entity
            /// </summary>
            public object Entity { get; set; }
            /// <summary>
            /// Store the param
            /// </summary>
            public dynamic Param { get; set; }
            /// <summary>
            /// Transacation of this procedure
            /// </summary>
            public DbTransaction Transaction { get; set; }
        }

        /// <summary>
        /// a private database entity default null
        /// </summary>
        private readonly Database _db = DbContextFactory.GetCurrentContext(ConnectionStringName);

        /// <summary>
        /// set connection string name, when you configuration many connection string, you should setting it's name in there.
        /// </summary>
        private const string ConnectionStringName = "sqlserver";

        /// <summary>
        /// Store for all the data for save to database in the future.
        /// </summary>
        private readonly Queue _storeQueue;

        /// <summary>
        /// commit the unit work
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, object> Commit()
        {

            //using (_db = DbContextFactory.GetCurrentContext(ConnectionStringName))
            using (_db)
            {
                //begin a trancation
                _db.BeginTransaction();
                var result = new Dictionary<int, object>();
                var i = 0;
                while (_storeQueue.Count != 0)
                {
                    var data = _storeQueue.Dequeue();
                    var d = data as QueueData;
                    if (d == null)
                    {
                        throw new Exception("program is error.");
                    }
                    switch (d.State)
                    {
                        case EntityState.Add:
                            result.Add(i++, _db.Insert(d.Entity));
                            break;
                        case EntityState.Edit:
                            result.Add(i++, _db.Update(d.Entity));
                            break;
                        case EntityState.Delete:
                            result.Add(i++, _db.Delete(d.Entity));
                            break;
                        case EntityState.Text:
                        case EntityState.StoredProcedure:
                            result.Add(i++, _db.Execute(d.Text, d.Param));
                            break;
                    }
                }
                _db.CompleteTransaction();
                return result;
            }
        }

        /// <summary>
        /// rollback the unit work
        /// </summary>
        public void Rollback()
        {
            _storeQueue.Clear();
        }

        /// <summary>
        /// Regist the entity's to the Queue
        /// when the commit method called,all the Queue's 
        /// entity will be mapped to sql
        /// </summary>
        /// <typeparam name="T">the type of entity</typeparam>
        /// <param name="entity">the entity to be regist</param>
        /// <param name="state">to add or delete or modified the entity</param>
        /// <returns>the key of commit result</returns>
        public int RegistEntity<T>(T entity, EntityState state)
        {
            var data = new QueueData { Entity = entity, State = state };
            _storeQueue.Enqueue(data);
            return _storeQueue.Count - 1;
        }
        #endregion

        #region Execute a sql

        /// <summary>
        /// Ecec the sql without anything returned
        /// </summary>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">the parames 4 the sql</param>
        /// <param name="transaction">this is the transaction</param>
        /// <returns>the rows effected</returns>
        public int ExecuteNonQuery(string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            return _db.Execute(strSql, param as object[]);
        }

        /// <summary>
        /// Ecec the sql with one object returned
        /// </summary>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">the parames 4 the sql</param>
        /// <param name="transaction">this is the transaction</param>
        /// <returns>the first row and the firt column ,it's the first cell's value</returns>
        public T ExecuteScalar<T>(string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            return _db.ExecuteScalar<T>(strSql, param as object[]);
        }
        #endregion

        #region Insert

        /// <summary>
        /// insert into database a entity
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="entity"> entity value, if strSql is not null,it's param in entity</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="transaction">this is transaction</param>
        /// <returns>the key of commit result</returns>
        public int Insert<T>(T entity, string strSql = null, DbTransaction transaction = null)
        {
            var data = new QueueData { Entity = entity, Text = strSql, State = EntityState.Add, Transaction = transaction };
            _storeQueue.Enqueue(data);
            return _storeQueue.Count - 1;
        }

        /// <summary>
        /// insert into database a entity list
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="entityList">entity value list</param>
        /// <param name="strSqlList">the sql text list,if it is not null, the sql must corresponding entityList</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>the key of commit result</returns>
        public List<int> Insert<T>(List<T> entityList, List<string> strSqlList = null, DbTransaction transaction = null)
        {
            var listResult = new List<int>();
            //strSqlList is not null
            if (strSqlList != null)
            {
                if (entityList.Count != strSqlList.Count)
                {
                    throw new Exception("if strSqlList not null, you must set entityList count is equal to strSqlList count!");
                }
                //get entity and correspding sql into queue
                for (var i = 0; i < entityList.Count; i++)
                {
                    var data = new QueueData { Entity = entityList[i], State = EntityState.Text, Text = strSqlList[i], Transaction = transaction };
                    _storeQueue.Enqueue(data);
                    listResult.Add(_storeQueue.Count - 1);
                }
            }
            else//strSqlList is null
            {
                //get entity into queue
                foreach (var data in entityList.Select(entity => new QueueData { Entity = entity, State = EntityState.Add, Transaction = transaction }))
                {
                    _storeQueue.Enqueue(data);
                    listResult.Add(_storeQueue.Count - 1);
                }
            }
            return listResult;
        }
        #endregion

        #region Edit

        /// <summary>
        /// edit entity by key,T is must be class in model,if you want edit with sql, please assign T
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="entity">new entity value, if strSql is not null,it's param in entity</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>the key of commit result</returns>
        public int Edit<T>(T entity, string strSql = null, DbTransaction transaction = null)
        {
            if (!typeof(T).IsClass)
            {
                throw new Exception("T is must be a class in model, if you want edit with sql, please assign T");
            }
            //Priority has been given to SQL statements
            var data = strSql == null
                ? new QueueData { Entity = entity, State = EntityState.Edit, Transaction = transaction }
                : new QueueData { Param = entity, State = EntityState.Text, Text = strSql, Transaction = transaction };

            _storeQueue.Enqueue(data);
            return _storeQueue.Count - 1;
        }

        /// <summary>
        /// edit entity list by key
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="entityList">the strSql param in new entity value list</param>
        /// <param name="strSqlList">the sql text list, if it is not null, the sql must corresponding entityList</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>the key of commit result</returns>
        public List<int> Edit<T>(List<T> entityList, List<string> strSqlList = null, DbTransaction transaction = null)
        {
            var listResult = new List<int>();
            //strSqlList is not null
            if (strSqlList != null)
            {
                if (entityList.Count != strSqlList.Count)
                {
                    throw new Exception("if strSqlList not null, you must set entityList count is equal to strSqlList count!");
                }
                //get entity and correspding sql into queue
                for (var i = 0; i < entityList.Count; i++)
                {
                    var data = new QueueData { Entity = entityList[i], State = EntityState.Text, Text = strSqlList[i], Transaction = transaction };
                    _storeQueue.Enqueue(data);
                    listResult.Add(_storeQueue.Count - 1);
                }
            }
            else//strSqlList is null
            {
                //get entity into queue
                foreach (var data in entityList.Select(entity => new QueueData { Entity = entity, State = EntityState.Edit, Transaction = transaction }))
                {
                    _storeQueue.Enqueue(data);
                    listResult.Add(_storeQueue.Count - 1);
                }
            }
            return listResult;
        }

        /// <summary>
        /// edit entity by key,T is must be class in model,if you want edit with sql, please assign T, You can pass in a sql str and param for the sql to update one or more entity.
        /// </summary>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">the param for strSql</param>
        /// <param name="transaction">this is transaction</param>
        /// <returns>the key of commit result</returns>
        public int Edit(string strSql, dynamic param, DbTransaction transaction = null)
        {
            var data = new QueueData { State = EntityState.Text, Param = param, Text = strSql, Transaction = transaction };
            _storeQueue.Enqueue(data);
            return _storeQueue.Count - 1;
        }

        /// <summary>
        /// edit by lambda, please assign T
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="lambdaParam">the sql set value param</param>
        /// <param name="lambdaWhere">the sql where param</param>
        /// <param name="transaction">this transacation</param>
        /// <returns>the key of commit result</returns>
        public int Edit<T>(Expression<Func<T>> lambdaParam, Expression<Func<T, bool>> lambdaWhere, DbTransaction transaction = null)
        {
            /*Here temporarily just by Lambda conversion to strings, spliced into a SQL statement.
             */
            //get param body
            var paramBody = lambdaParam.Body as MemberInitExpression;
            if (paramBody == null)
            {
                throw new NullReferenceException("param is must be not null.");
            }
            //get body collection
            var bodyCollection = paramBody.Bindings;
            //register a where string, if whereLambda is null, it will not append where into sql.
            var whereBody = "";
            if (lambdaWhere != null)
            {
                //get where body string
                var lambdaWhereStr = lambdaWhere.Body.ToString();
                //get lambda param name
                var lambdaWhereParam = lambdaWhere.Parameters[0].Name;
                //replace not use str
                whereBody = lambdaWhereStr.Replace("(", "").Replace(")", "").Replace("AndAlso", "and").Replace("==", "=").Replace(lambdaWhereParam + ".", "");

            }
            //string build for sql
            var sb = new StringBuilder();

            var paramlist = new List<string>();
            sb.Append(string.Format("update {0} set ", typeof(T).Name));
            for (var i = 0; i < bodyCollection.Count; i++)
            {
                //get param 
                var match = Regex.Match(bodyCollection[i].ToString(), "=(.+)");
                if (match.Success)
                {
                    paramlist.Add(match.Groups[1].Value.Replace("\"", "").Replace("Convert(", "").Replace(")", ""));
                }
                else
                {
                    throw new Exception("couldn't find param value.");
                }

                sb.Append(i == bodyCollection.Count - 1
                    ? Regex.Replace(bodyCollection[i].ToString(), "=(.+)", string.Format(" = @a{0} ", i))
                    : Regex.Replace(bodyCollection[i].ToString(), "=(.+)", string.Format(" = @a{0}, ", i)));
            }
            if (lambdaWhere != null)
            {
                sb.Append(string.Format("where {0}", whereBody));
            }
            string sql = sb.ToString();
            //build data into storeQueue
            var data = new QueueData { Param = ListToObject(paramlist), State = EntityState.Text, Text = sql, Transaction = transaction };
            _storeQueue.Enqueue(data);
            return _storeQueue.Count - 1;
        }

        /// <summary>
        /// list to object, Number of collection is temporarily limited to 11.
        /// </summary>
        /// <param name="list">the param for object</param>
        /// <returns>object result</returns>
        private object ListToObject(IReadOnlyList<string> list)
        {
            switch (list.Count)
            {
                case 1:
                    return new { a0 = list[0] };
                case 2: return new { a0 = list[0], a1 = list[1] };
                case 3: return new { a0 = list[0], a1 = list[1], a2 = list[2] };
                case 4: return new { a0 = list[0], a1 = list[1], a2 = list[2], a3 = list[3] };
                case 5: return new { a0 = list[0], a1 = list[1], a2 = list[2], a3 = list[3], a4 = list[4] };
                case 6: return new { a0 = list[0], a1 = list[1], a2 = list[2], a3 = list[3], a4 = list[4], a5 = list[5] };
                case 7: return new { a0 = list[0], a1 = list[1], a2 = list[2], a3 = list[3], a4 = list[4], a5 = list[5], a6 = list[6] };
                case 8: return new { a0 = list[0], a1 = list[1], a2 = list[2], a3 = list[3], a4 = list[4], a5 = list[5], a6 = list[6], a7 = list[7] };
                case 9: return new { a0 = list[0], a1 = list[1], a2 = list[2], a3 = list[3], a4 = list[4], a5 = list[5], a6 = list[6], a7 = list[7], a8 = list[8] };
                case 10: return new { a0 = list[0], a1 = list[1], a2 = list[2], a3 = list[3], a4 = list[4], a5 = list[5], a6 = list[6], a7 = list[7], a8 = list[8], a9 = list[9] };
                default: return new { a0 = list[0], a1 = list[1], a2 = list[2], a3 = list[3], a4 = list[4], a5 = list[5], a6 = list[6], a7 = list[7], a8 = list[8], a9 = list[9], a10 = list[10] };
            }
        }
        #endregion

        #region Delete

        /// <summary>
        /// delete by entity key
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="entity">entity value, if strSqlis not null, strSql param in entity</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>the key of commit result</returns>
        public int Delete<T>(T entity, string strSql = null, DbTransaction transaction = null)
        {
            //Priority has been given to SQL statements
            var data = strSql == null
                ? new QueueData { Entity = entity, State = EntityState.Delete, Transaction = transaction }
                : new QueueData { Param = entity, State = EntityState.Text, Transaction = transaction, Text = strSql };
            _storeQueue.Enqueue(data);
            return _storeQueue.Count - 1;
        }

        /// <summary>
        /// delete by entity key list
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="entityList">entity value list,if strSqlList is not null, the sql must corresponding entityList</param>
        /// <param name="strSqlList">the sql text list</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>the key of commit result</returns>
        public List<int> Delete<T>(List<T> entityList, List<string> strSqlList = null, DbTransaction transaction = null)
        {
            var listResult = new List<int>();
            //strSqlList is not null
            if (strSqlList != null)
            {
                if (entityList.Count != strSqlList.Count)
                {
                    throw new Exception("entity collection count must corresponding sql collection.");
                }
                //get entity and correspding sql into queue
                for (var i = 0; i < entityList.Count; i++)
                {
                    _storeQueue.Enqueue(new QueueData { State = EntityState.Text, Param = entityList[i], Text = strSqlList[i], Transaction = transaction });
                    listResult.Add(_storeQueue.Count - 1);
                }
            }
            else//strSqlList is null
            {
                foreach (var entity in entityList)
                {
                    _storeQueue.Enqueue(new QueueData { Entity = entity, State = EntityState.Delete, Transaction = transaction });
                    listResult.Add(_storeQueue.Count - 1);
                }
            }
            return listResult;
        }

        /// <summary>
        /// delete by sql
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">the param for strSql</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>the key of commit result</returns>
        public int Delete<T>(string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            _storeQueue.Enqueue(new QueueData { Text = strSql, State = EntityState.Delete, Transaction = transaction, Param = param });
            return _storeQueue.Count - 1;
        }

        /// <summary>
        /// delete by lambda
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="lambdaWhere">delete condition</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>the key of commit result</returns>
        public int Delete<T>(Expression<Func<T, bool>> lambdaWhere, DbTransaction transaction = null)
        {
            //get where body string
            var lambdaWhereStr = lambdaWhere.Body.ToString();
            //get lambda param name
            var lambdaWhereParam = lambdaWhere.Parameters[0].Name;
            //replace not use str
            var whereBody = lambdaWhereStr.Replace("(", "").Replace(")", "").Replace("AndAlso", "and").Replace("==", "=").Replace(lambdaWhereParam + ".", "");
            var data = new QueueData { State = EntityState.Text, Text = string.Format("delete from {0} where {1}", typeof(T).Name, whereBody), Transaction = transaction };
            _storeQueue.Enqueue(data);
            return _storeQueue.Count - 1;
        }
        #endregion

        #region Query

        /// <summary>
        /// query by param
        /// </summary>
        /// <typeparam name="T">entity type, and return this type.</typeparam>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">the param for strSql</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<T> Query<T>(string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            //_db = DbContextFactory.GetCurrentContext(ConnectionStringName);
            return _db.Query<T>(strSql, param);
        }

        /// <summary>
        /// query with content entity
        /// </summary>
        /// <typeparam name="TFirst">first entity type</typeparam>
        /// <typeparam name="TSecond">second entity type</typeparam>
        /// <typeparam name="TResult">return entity type</typeparam>
        /// <param name="cb">A callback function to connect the entity instances, or null to automatically guess the relationships</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">param for strSql</param>
        /// <param name="transaction">this is trancation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<TResult> Query<TFirst, TSecond, TResult>(Func<TFirst, TSecond, TResult> cb, string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            //_db = DbContextFactory.GetCurrentContext(ConnectionStringName);
            return _db.Query<TFirst, TSecond, TResult>(cb, strSql, param);
        }

        /// <summary>
        /// query with content entity
        /// </summary>
        /// <typeparam name="TFirst">first entity type</typeparam>
        /// <typeparam name="TSecond">second entity type</typeparam>
        /// <typeparam name="TThird">third entity type</typeparam>
        /// <typeparam name="TResult">return entity type</typeparam>
        /// <param name="cb">A callback function to connect the entity instances, or null to automatically guess the relationships</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">param for strSql</param>
        /// <param name="transaction">this is trancation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<TResult> Query<TFirst, TSecond, TThird, TResult>(Func<TFirst, TSecond, TThird, TResult> cb, string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            //_db = DbContextFactory.GetCurrentContext(ConnectionStringName);
            return _db.Query<TFirst, TSecond, TThird, TResult>(cb, strSql, param);
        }

        /// <summary>
        /// query with content entity
        /// </summary>
        /// <typeparam name="TFirst">first entity type</typeparam>
        /// <typeparam name="TSecond">second entity type</typeparam>
        /// <typeparam name="TThird">third entity type</typeparam>
        /// <typeparam name="TFourth">fourth entity type</typeparam>
        /// <typeparam name="TResult">return entity type</typeparam>
        /// <param name="cb">A callback function to connect the entity instances, or null to automatically guess the relationships</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">param for strSql</param>
        /// <param name="transaction">this is trancation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<TResult> Query<TFirst, TSecond, TThird, TFourth, TResult>(Func<TFirst, TSecond, TThird, TFourth, TResult> cb, string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            //_db = DbContextFactory.GetCurrentContext(ConnectionStringName);
            return _db.Query<TFirst, TSecond, TThird, TFourth, TResult>(cb, strSql, param);
        }

        /// <summary>
        /// this is query page used method, it's load data by sql and performs a multi-entity query.
        /// </summary>
        /// <typeparam name="T">return entity type</typeparam>
        /// <param name="pageIndex">the current page number</param>
        /// <param name="pageCount">the count in this page</param>
        /// <param name="total">the all count in database</param>
        /// <param name="types">multi array in sql</param>
        /// <param name="cb">A callback function to connect the entity instances, or null to automatically guess the relationships</param>
        /// <param name="sql">the sql text</param>
        /// <param name="param">this is the param</param>
        /// <returns>data collection as IEnumerable</returns>
        public IEnumerable<T> Query<T>(int pageIndex, int pageCount, out long total, Type[] types, object cb, string sql, dynamic param)
        {
            //_db = DbContextFactory.GetCurrentContext(ConnectionStringName);
            string sqlCount, sqlPage;
            var args = param as object[] ?? new[] { param as object };
            //build page sql and cout sql.
            _db.BuildPageQueries<T>(pageIndex * pageCount - pageCount, pageCount, sql, ref args, out sqlCount, out sqlPage);
            total = _db.SingleOrDefault<long>(sqlCount, args);
            return _db.Query<T>(types, cb, sql, param);
        }

        #endregion

        #region Query page

        /// <summary>
        /// query page by param
        /// </summary>
        /// <typeparam name="T">entity type, and return this type.</typeparam>
        /// <param name="pageIndex">the current page number</param>
        /// <param name="pageCount">the count in this page</param>
        /// <param name="total">the all count in database</param>
        /// <param name="strSql">the sql text,can be null.</param>
        /// <param name="param">the param in strSql</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<T> QueryPage<T>(int pageIndex, int pageCount, out long total, string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            //_db = DbContextFactory.GetCurrentContext(ConnectionStringName);
            string sqlCount, sqlPage;
            var args = param as object[] ?? new[] { param as object };
            //build page sql and cout sql.
            _db.BuildPageQueries<T>(pageIndex * pageCount - pageCount, pageCount, strSql, ref args, out sqlCount, out sqlPage);
            total = _db.SingleOrDefault<long>(sqlCount, args);
            return _db.Query<T>(sqlPage, args);
        }

        /// <summary>
        /// query page with content entity
        /// </summary>
        /// <typeparam name="TFirst">first entity type</typeparam>
        /// <typeparam name="TSecond">second entity type</typeparam>
        /// <typeparam name="TResult">return entity type</typeparam>
        /// <param name="cb">A callback function to connect the entity instances, or null to automatically guess the relationships</param>
        /// <param name="pageIndex">the current page number</param>
        /// <param name="pageCount">the count in this page</param>
        /// <param name="total">the all count in database</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">the param in strSql</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<TResult> QueryPage<TFirst, TSecond, TResult>(int pageIndex, int pageCount, out long total, Func<TFirst, TSecond, TResult> cb, string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            return Query<TResult>(pageIndex, pageCount, out total, new[] { typeof(TFirst), typeof(TSecond) }, cb, strSql, param);
        }

        /// <summary>
        /// query page with content entity
        /// </summary>
        /// <typeparam name="TFirst">first entity type</typeparam>
        /// <typeparam name="TSecond">second entity type</typeparam>
        /// <typeparam name="TThird">third entity type</typeparam>
        /// <typeparam name="TResult">return entity type</typeparam>
        /// <param name="cb">A callback function to connect the entity instances, or null to automatically guess the relationships</param>
        /// <param name="pageIndex">the current page number</param>
        /// <param name="pageCount">the count in this page</param>
        /// <param name="total">the all count in database</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">the param in strSql</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<TResult> QueryPage<TFirst, TSecond, TThird, TResult>(int pageIndex, int pageCount, out long total, Func<TFirst, TSecond, TThird, TResult> cb, string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            return Query<TResult>(pageIndex, pageCount, out total, new[] { typeof(TFirst), typeof(TSecond), typeof(TThird) }, cb, strSql, param);
        }

        /// <summary>
        /// query page witn content entity
        /// </summary>
        /// <typeparam name="TFirst">first entity type</typeparam>
        /// <typeparam name="TSecond">second entity type</typeparam>
        /// <typeparam name="TThird">third entity type</typeparam>
        /// <typeparam name="TFourth">fourth entity type</typeparam>
        /// <typeparam name="TResult">return entity type</typeparam>
        /// <param name="cb">A callback function to connect the entity instances, or null to automatically guess the relationships</param>
        /// <param name="pageIndex">the current page number</param>
        /// <param name="pageCount">the count in this page</param>
        /// <param name="total">the all count in database</param>
        /// <param name="strSql">the sql text</param>
        /// <param name="param">the param in strSql</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<TResult> QueryPage<TFirst, TSecond, TThird, TFourth, TResult>(int pageIndex, int pageCount, out long total, Func<TFirst, TSecond, TThird, TFourth, TResult> cb, string strSql, dynamic param = null, DbTransaction transaction = null)
        {
            return Query<TResult>(pageIndex, pageCount, out total, new[] { typeof(TFirst), typeof(TSecond), typeof(TThird), typeof(TFourth) }, cb, strSql, param);
        }

        #endregion

        #region Query by lambda
        /// <summary>
        /// query by lambda
        /// </summary>
        /// <typeparam name="T">entity type, and return this type.</typeparam>
        /// <param name="lambdaWhere">this is where lambda to query</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<T> QueryLambda<T>(Expression<Func<T, bool>> lambdaWhere, DbTransaction transaction = null)
        {
            /*Here temporarily just by Lambda conversion to strings, spliced into a SQL statement.
            */
            //register a where string, if whereLambda is null, it will not append where into sql.
            var whereBody = "";
            if (lambdaWhere != null)
            {
                //get where body string
                var lambdaWhereStr = lambdaWhere.Body.ToString();
                //get lambda param name
                var lambdaWhereParam = lambdaWhere.Parameters[0].Name;
                //replace not use str
                whereBody = lambdaWhereStr.Replace("(", "").Replace(")", "").Replace("AndAlso", "and").Replace("==", "=").Replace(lambdaWhereParam + ".", "");
            }
            //todo:There should remove navigation property.
            //get all the property in T
            var t = typeof(T).GetProperties();
            var columns = t.Select(item => item.Name).ToList();
            var sql = string.IsNullOrEmpty(whereBody) ? string.Format("select {0} from {1} ", string.Join(",", columns), typeof(T).Name) : string.Format("select {0} from {1} where {2}", string.Join(",", columns), typeof(T).Name, whereBody);
            return _db.Query<T>(sql);
        }

        /// <summary>
        /// query page by lambda
        /// </summary>
        /// <typeparam name="T">entity type</typeparam>
        /// <param name="pageIndex">the current page number</param>
        /// <param name="pageCount">the count in this page</param>
        /// <param name="total">the all count in database</param>
        /// <param name="lambdaWhere">this is where lambda to query</param>
        /// <param name="transaction">this is transacation</param>
        /// <returns>A data collection as IEnumerable</returns>
        public IEnumerable<T> QueryLambdaPage<T>(int pageIndex, int pageCount, out int total, Expression<Func<T, bool>> lambdaWhere, DbTransaction transaction = null)
        {
            throw new NotImplementedException();
        }
        #endregion
    }
}